import streamlit as st
import pymysql

# 配置数据库连接（请替换为你的实际信息）
DB_CONFIG = {
    "host": "localhost:3306",
    "user": "root",
    "password": "123456",
    "database": "ukraine_conflict",
    "charset": "utf8mb4"
}


def get_data(sql_query):
    """从数据库获取数据"""
    try:
        conn = pymysql.connect(**DB_CONFIG)
        with conn.cursor() as cursor:
            cursor.execute(sql_query)
            data = cursor.fetchall()
            columns = [desc[0] for desc in cursor.description]
        return columns, data
    except Exception as e:
        st.error(f"数据库连接失败: {str(e)}")
        return None, None
    finally:
        if 'conn' in locals():
            conn.close()


def main():
    st.set_page_config(page_title="乌克兰冲突数据分析大屏", layout="wide")
    st.title("乌克兰冲突数据分析大屏 📊")

    # 分两列布局
    col1, col2 = st.columns(2)

    # 表格1：按事件类型统计总数
    with col1:
        st.subheader("1. 事件类型统计")
        sql = """
        SELECT event_type, COUNT(*) AS total_events
        FROM russia_ukraine_conflict
        GROUP BY event_type;
        """
        cols, data = get_data(sql)
        if data:
            st.table(data=dict(zip(cols, ["类型", "总数"])) | {"rows": data})

    # 表格2：按参与方统计事件数
    with col2:
        st.subheader("2. 参与方事件统计")
        sql = """
        SELECT involved_parties, COUNT(*) AS total_events
        FROM russia_ukraine_conflict
        GROUP BY involved_parties;
        """
        cols, data = get_data(sql)
        if data:
            st.table(data=dict(zip(cols, ["参与方", "事件数"])) | {"rows": data})

    # 表格3：按省份统计死亡人数
    with col1:
        st.subheader("3. 省份死亡人数统计")
        sql = """
        SELECT province, SUM(fatalities) AS total_fatalities
        FROM russia_ukraine_conflict
        GROUP BY province;
        """
        cols, data = get_data(sql)
        if data:
            st.table(data=dict(zip(cols, ["省份", "死亡总数"])) | {"rows": data})

    # 表格4：按月份统计事件趋势
    with col2:
        st.subheader("4. 月度事件趋势")
        sql = """
        SELECT 
            DATE_FORMAT(event_date, '%Y-%m') AS month,
            COUNT(*) AS total_events
        FROM russia_ukraine_conflict
        GROUP BY YEAR(event_date), MONTH(event_date);
        """
        cols, data = get_data(sql)
        if data:
            st.table(data=dict(zip(cols, ["月份", "事件数"])) | {"rows": data})

    # 表格5：高死亡事件Top5
    with col1:
        st.subheader("5. 高死亡事件Top5")
        sql = """
        SELECT event_date, location_data, fatalities, detailed_notes
        FROM russia_ukraine_conflict
        WHERE fatalities > 0
        ORDER BY fatalities DESC
        LIMIT 5;
        """
        cols, data = get_data(sql)
        if data:
            st.table(data=dict(zip(cols, ["日期", "地点", "死亡人数", "事件描述"])) | {"rows": data})

    # 表格6：近期事件动态（最近7天）
    with col2:
        st.subheader("6. 近期事件动态（近7天）")
        sql = """
        SELECT event_date, event_type, involved_parties, location_data
        FROM russia_ukraine_conflict
        WHERE event_date >= CURDATE() - INTERVAL 7 DAY
        ORDER BY event_date DESC;
        """
        cols, data = get_data(sql)
        if data:
            st.table(data=dict(zip(cols, ["日期", "类型", "参与方", "地点"])) | {"rows": data})


if __name__ == "__main__":
    main()